package site.sorghum.ddl.dialect;

import site.sorghum.ddl.entity.DdlColumnWrap;
import site.sorghum.ddl.entity.DdlIndexWrap;
import site.sorghum.ddl.entity.DdlTableWrap;
import site.sorghum.ddl.enums.DbType;
import site.sorghum.ddl.field.SQLServerTypeConvert;
import site.sorghum.ddl.field.TypeConverter;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;

public class SQLServerDDLDialect extends AbstractDDLDialect {
    @Override
    public DbType getDatabaseType() {
        return DbType.SQLSERVER;
    }

    @Override
    public TypeConverter typeConverter() {
        return SQLServerTypeConvert.INSTANCE;

    }

    @Override
    protected String quoteIdentifier(String identifier) {
        return "[" + identifier + "]";
    }

    @Override
    protected String quoteValue(Object value) {
        if (value instanceof String) {
            return "'" + ((String) value).replace("'", "''") + "'";
        }
        return String.valueOf(value);
    }

    @Override
    protected String getColumnCommentClause(DdlColumnWrap column) {
        // SQL Server 列注释需要单独的 EXEC sp_addextendedproperty 语句
        return "";
    }

    @Override
    public List<String> generateCreateTableDDL(DdlTableWrap table) {
        List<String> result = new ArrayList<>();
        StringBuilder sql = new StringBuilder(super.generateCreateTableDDL(table).get(0));

        // 添加表注释
        if (table.getRemarks() != null && !table.getRemarks().isEmpty()) {
            sql.append(";\nEXEC sp_addextendedproperty 'MS_Description', ").append(quoteValue(table.getRemarks())).append(", 'SCHEMA', 'dbo', 'TABLE', ").append(quoteIdentifier(table.getName()));
        }

        // 添加列注释
        for (DdlColumnWrap column : table.getColumns()) {
            if (column.getRemarks() != null && !column.getRemarks().isEmpty()) {
                sql.append(";\nEXEC sp_addextendedproperty 'MS_Description', ").append(quoteValue(column.getRemarks())).append(", 'SCHEMA', 'dbo', 'TABLE', ").append(quoteIdentifier(table.getName())).append(", 'COLUMN', ").append(quoteIdentifier(column.getName()));
            }
        }

        if (!Objects.equals(sql.charAt(sql.length() - 1), ';')) {
            sql.append(";");
        }
        result.add(sql.toString());
        for (DdlIndexWrap index : table.getIndexes()) {
            String generateIndexDDL = generateAddIndexDDL(index);
            result.add(generateIndexDDL);
        }
        return result;
    }

    @Override
    public String generateAddIndexDDL(String indexName, List<DdlColumnWrap> columnWraps, boolean unique) {
        if (columnWraps.isEmpty()) {
            return "";
        }

        String tableName = columnWraps.get(0).getTable();
        if (indexName == null|| indexName.isEmpty()){
            indexName = DdlIndexWrap.columnUniqueIndexName(columnWraps.stream().map(DdlColumnWrap::getName).collect(Collectors.toList()), unique);
        }
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE ");

        if (unique) {
            sql.append("UNIQUE ");
        }

        sql.append("INDEX ")
                .append(quoteIdentifier(indexName))
                .append(" ON ")
                .append(quoteIdentifier(tableName))
                .append(" (")
                .append(columnWraps.stream()
                        .map(DdlColumnWrap::getName)
                        .map(this::quoteIdentifier)
                        .collect(Collectors.joining(", ")))
                .append(")");

        sql.append(";");
        return sql.toString();
    }

    @Override
    public String generateDropColumnDDL(String tableName, String columnName) {
        return "ALTER TABLE " +
                quoteIdentifier(tableName) +
                " DROP COLUMN " +
                quoteIdentifier(columnName) +
                ";";
    }

    @Override
    public String generateDropIndexDDL(String tableName, String indexName) {
        return "DROP INDEX " +
                quoteIdentifier(indexName) +
                " ON " +
                quoteIdentifier(tableName) +
                ";";
    }

    @Override
    public List<String> fetchAllTableNames(String schema, Function<String, List<String>> tables) {
        String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " +
                "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '" +
                (schema != null ? schema : "dbo") + "'";
        return tables.apply(sql);
    }


    @Override
    public DdlTableWrap fromDataSource(
            String schema,
            String tableName,
            Function<String, List<String>> pks,
            Function<String, DdlTableWrap> table,
            Function<String, List<DdlColumnWrap>> columns,
            Function<String, List<DdlIndexWrap>> indexes) {
        // 生成获取当前表信息的语句SQL
        String tableSql = String.format("SELECT TABLE_NAME    AS name,\n" +
                "       TABLE_COMMENT AS remarks\n" +
                "FROM INFORMATION_SCHEMA.TABLES\n" +
                "WHERE TABLE_NAME = '%s';", tableName);
        // 生成获取列信息的SQL
        String columnSql = String.format("SELECT '%s'                   as tableName,\n" +
                "       COLUMN_NAME              AS name,\n" +
                "       DATA_TYPE                AS dbType,\n" +
                "       CHARACTER_MAXIMUM_LENGTH AS size,\n" +
                "       NUMERIC_PRECISION        AS digit,\n" +
                "       IS_NULLABLE              AS isNullable,\n" +
                "       COLUMN_DEFAULT           AS defaultValue,\n" +
                "       COLUMN_COMMENT           AS remarks\n" +
                "FROM INFORMATION_SCHEMA.COLUMNS\n" +
                "WHERE TABLE_NAME = '%s';", tableName, tableName);
        // 生成主键获取信息的SQL
        String pksSql = String.format("SELECT COLUMN_NAME AS name\n" +
                "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n" +
                "WHERE TABLE_NAME = '%s'\n" +
                "  AND CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME\n" +
                "                         FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS\n" +
                "                         WHERE TABLE_NAME = '%s'\n" +
                "                           AND CONSTRAINT_TYPE = 'PRIMARY KEY');", tableName, tableName);
        String indexSql = String.format("SELECT '%s'                                                 AS tableName,\n" +
                "       i.name                                                         AS indexName,\n" +
                "       STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columnNames,\n" +
                "       CASE WHEN i.is_unique = 1 THEN 1 ELSE 0 END                    AS isUnique\n" +
                "FROM sys.indexes i\n" +
                "         INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id\n" +
                "         INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id\n" +
                "         INNER JOIN sys.tables t ON i.object_id = t.object_id\n" +
                "WHERE t.name = '%s'\n" +
                "  AND i.name IS NOT NULL\n" +
                "  AND i.is_primary_key = 0\n" +
                "GROUP BY t.name, i.name, i.is_unique;", tableName, tableName);
        DdlTableWrap tableWrap = table.apply(tableSql);
        List<DdlColumnWrap> columnWraps = columns.apply(columnSql);
        List<String> pkList = pks.apply(pksSql);
        List<DdlIndexWrap> indexWraps = indexes.apply(indexSql);
        tableWrap.setColumns(columnWraps);
        tableWrap.setPks(pkList);
        tableWrap.setIndexes(indexWraps);
        return tableWrap;
    }
}